<?php
/**
 *备货规则-分类规则模型,主要是用来筛选符合备货规则中的分类规则的商品
 *@author shipanpan
 */
class Cron_Model_Backup_BackupRuleFilterRule extends Model_Base {
	private $_backupRuleModel;
	public function __construct() {
		$this->db = $this->db();
		$this->writeDb = $this->db("write_db");
	}

	/**
	 * 获取某条备货规则的所有分类规则
	 * @param  int    $backupRuleId 备货规则id(backup_rule表id)
	 * @return array
	 */
	public function getByBackupRuleId(int $backupRuleId) {
		return $this->db->fetchAll("select * from b_product_backup_rule_filter_rule where backup_rule_id=?", $backupRuleId);
	}

	/**
	 * 获取一个仓库中符合备货规则的分类规则的商品总数
	 * @param  int    $backupRuleId  [description]
	 * @param  [type] $warehouseCode [description]
	 * @return int
	 */
	public function getWarehouseMatchGoodsTotalNum(int $backupRuleId, $warehouseCode) {
		if (!$this->_getWarehouseMatchGoodsCheck($backupRuleId, $warehouseCode)) {
			return false;
		}
		$backupRuleParseResult = $this->_parseRule($backupRuleId, $warehouseCode);
		if ($backupRuleParseResult === false) {
			return false;
		}
		$tableSql = "select count(*) as num from tmp_30days_has_sales_product" . $backupRuleParseResult['tableSql'];
		$whereSql = " where tmp_30days_has_sales_product.warehouse=" . $this->db->quote($warehouseCode) . ' AND tmp_30days_has_sales_product.is_count_c_story=0';
		$whereSql .= $backupRuleParseResult['whereSql'];

		$backupRuleSql = $tableSql . $whereSql;
		//考虑可能的主从延迟，查询写库
		return $this->writeDb->fetchOne($backupRuleSql);
	}

	/**
	 * 获取一个仓库中符合备货规则的分类规则的商品
	 * @param  [type] $backupRuleId 备货规则id(backup_rule表id)
	 * @param  [type] $warehouseCode 备货规则适用仓库
	 * @param  int    $offset        返回记录偏移量
	 * @param  int    $length        返回记录条数
	 * @return [type]                [description]
	 */
	public function getWarehouseMatchGoods(int $backupRuleId, $warehouseCode, int $offset, int $length) {
		if (!$this->_getWarehouseMatchGoodsCheck($backupRuleId, $warehouseCode)) {
			return false;
		}
		$backupRuleParseResult = $this->_parseRule($backupRuleId, $warehouseCode);
		if ($backupRuleParseResult === false) {
			return false;
		}

		$tableSql = "select tmp_30days_has_sales_product.goods_sn from tmp_30days_has_sales_product" . $backupRuleParseResult['tableSql'];
		$whereSql = " where tmp_30days_has_sales_product.warehouse=" . $this->db->quote($warehouseCode) . ' AND tmp_30days_has_sales_product.is_count_c_story=0';
		$whereSql .= $backupRuleParseResult['whereSql'];

		$backupRuleSql = $tableSql . $whereSql . " limit $offset,$length";
		//考虑可能的主从延迟，查询写库
		$matchGoodsId = $this->writeDb->fetchAll($backupRuleSql);

		return array_column($matchGoodsId, 'goods_sn');
	}

	public function _getWarehouseMatchGoodsCheck(int $backupRuleId, $warehouseCode) {
		$this->_backupRuleModel || $this->_backupRuleModel = new Cron_Model_Backup_BackupRule();
		$ruleHasWarehouse = $this->_backupRuleModel->ruleApplyWarehouse($backupRuleId);
		if (!$ruleHasWarehouse) {
			return $this->_error($this->_backupRuleModel->getErrorMsg());
		}

		if (!in_array($warehouseCode, $ruleHasWarehouse)) {
			return $this->_error("你选择的仓库的不在备货规则的适用仓库内！");
		}
		return true;
	}

	/**
	 * 解析备货规则的分类规则(找出使用备货规则的商品筛选规则)，将其转换成sql语句
	 * @param  [type] $backupRuleId [description]
	 * @param  [type] $warehouse    [description]
	 * @return 出错返回false，成功返回['tableSql' =>'' , 'whereSql' =>'']
	 */
	private function _parseRule($backupRuleId, $warehouse) {
		$backupFilterRule = $this->getByBackupRuleId($backupRuleId);
		if (!$backupFilterRule) {
			return $this->_error('未找到备货规则的分类规则！');
		}

		$parseRuleHasError = false;

		$joinTableSql = [];
		$RuleWhereSql = "";
		$RuleTableSql = "";
		foreach ($backupFilterRule as $oneFilterRule) {
			$filterType = $oneFilterRule['filter_type'];
			$ruleParseMethod = $this->_getFilterTypeParseMethodName($filterType);
			if (!$ruleParseMethod) {
				$parseRuleHasError = true;
				break;
			}

			$parseResult = $this->$ruleParseMethod($oneFilterRule);
			if ($parseResult === false) {
				$parseRuleHasError = true;
				break;
			}
			if ($parseResult['tableSql']) {
				$joinTableSql[] = $parseResult['tableSql'];
			}

			if ($parseResult['whereSql']) {
				$RuleWhereSql .= " AND " . $parseResult['whereSql'] . " ";
			}
		}

		if ($parseRuleHasError) {
			return $this->_error("备货规则的分类规则解析出错，原因是" . $this->getErrorMsg());
		}

		$joinTableSql = array_unique($joinTableSql);
		$RuleTableSql .= implode(" ", $joinTableSql);

		return ['tableSql' => $RuleTableSql, 'whereSql' => $RuleWhereSql];
	}

	/**
	 * 获取分类规则过滤字段规则解析的方法
	 * @param  [type] $filterType 过滤字段
	 * @return 成功返回方法名，未找到返回false
	 */
	private function _getFilterTypeParseMethodName($filterType) {
		$typeParseMethodMap = [
			'product_category' => '_productTableFieldsParse',
			'recmd_level' => '_productTableFieldsParse',

			'product_state' => '_productPurchaseTableFieldsParse',
			'product_price' => '_productPurchaseTableFieldsParse',

			'product_level' => '_tmp30DyasSalesTableFieldsParse',
			'product_sales' => '_tmp30DyasSalesTableFieldsParse',
			'product_stock' => '_tmp30DyasSalesTableFieldsParse',
			'live_age' => '_tmp30DyasSalesTableFieldsParse',

			'chinabrands' => '_webUpShelfTableFieldsParse',
		];

		if (!isset($typeParseMethodMap[$filterType])) {
			return $this->_error("未找到类别：" . $filterType . "的解析方法！");
		}
		return $typeParseMethodMap[$filterType];
	}

	/**
	 *product表字段过滤规则解析
	 * @param  [type] $oneFilterRule [description]
	 * @return [type]                [description]
	 */
	private function _productTableFieldsParse($oneFilterRule) {
		$sqlResult = ['tableSql' => "", "whereSql" => ''];
		$filterType = $oneFilterRule['filter_type'];
		if (!in_array($filterType, ['product_category', 'recmd_level'])) {
			return $sqlResult;
		}

		$table = 'p_product.';
		$fields = [
			'recmd_level' => $table . 'recommend_level',
			'product_category' => $table . 'catalog_id',
		];

		$sqlResult['tableSql'] = " left join p_product on tmp_30days_has_sales_product.goods_sn=p_product.goods_sn ";

		$sqlResult['whereSql'] = $this->_whereSqlParse($oneFilterRule, $fields[$filterType]);
		return $sqlResult;

	}

	/**
	 * product_purchase表字段过滤规则解析
	 * @param  [type] $oneFilterRule [description]
	 * @return [type]                [description]
	 */
	private function _productPurchaseTableFieldsParse($oneFilterRule) {
		$sqlResult = ['tableSql' => "", "whereSql" => ''];
		$filterType = $oneFilterRule['filter_type'];
		if (!in_array($filterType, ['product_state', 'product_price'])) {
			return $sqlResult;
		}

		$table = 'p_product_purchase.';
		$fields = [
			'product_state' => $table . 'goods_resource_status',
			'product_price' => $table . 'purchase_price',
		];

		$sqlResult['tableSql'] = " left join p_product_purchase on tmp_30days_has_sales_product.goods_sn=p_product_purchase.goods_sn ";
		$sqlResult['whereSql'] = $this->_whereSqlParse($oneFilterRule, $fields[$filterType]);
		return $sqlResult;
	}

	/**
	 * (tmp_30days_has_sales_product)30天内有销量产品表解析
	 * @param  [type] $oneFilterRule [description]
	 * @return [type]                [description]
	 */
	private function _tmp30DyasSalesTableFieldsParse($oneFilterRule) {
		$sqlResult = ['tableSql' => "", "whereSql" => ''];
		$filterType = $oneFilterRule['filter_type'];
		if (!in_array($filterType, ['product_sales', 'product_stock', 'live_age', 'product_level'])) {
			return $sqlResult;
		}
		$table = 'tmp_30days_has_sales_product.';
		$fields = [
			'product_sales' => $table . 'sales_quantity',
			'product_stock' => $table . 'stock',
			'live_age' => $table . 'lefe_cycle',
			'product_level' => $table . 'product_grade',
		];

		$sqlResult['whereSql'] = $this->_whereSqlParse($oneFilterRule, $fields[$filterType]);
		return $sqlResult;
	}

	/**
	 * web_up_shelf表字段解析
	 * @param  [type] $oneFilterRule [description]
	 * @return [type]                [description]
	 */
	private function _webUpShelfTableFieldsParse($oneFilterRule) {
		$sqlResult = ['tableSql' => "", "whereSql" => ''];
		$filterType = $oneFilterRule['filter_type'];
		if (!in_array($filterType, ['chinabrands'])) {
			return $sqlResult;
		}

		$table = 'web_up_shelf.';
		$fields = [
			'chinabrands' => $table . 'type',
		];

		$sqlResult['tableSql'] = ' left join web_up_shelf on tmp_30days_has_sales_product.goods_sn=web_up_shelf.goods_sn ';
		$sqlResult['whereSql'] = $this->_whereSqlParse($oneFilterRule, $fields[$filterType]);
		return $sqlResult;
	}

	private function _whereSqlParse($oneFilterRule, $tableFields) {
		$ruleOperatorParseMethod = "_" . $oneFilterRule['operator'] . "Operator";
		$operatorSql = $this->$ruleOperatorParseMethod($oneFilterRule);
		if (trim($operatorSql)) {
			return str_replace('%filterField%', $tableFields, $operatorSql);
		}
		return $operatorSql;
	}

	/**
	 * 小于
	 * @param  array $oneFilterRule 备货规则的一条分类规则
	 * @return string
	 */
	private function _ltOperator(array $oneFilterRule) {
		list($operator, $ltVal) = [$oneFilterRule['operator'], $oneFilterRule['operator_min_param']];
		if ($operator !== 'lt') {
			return '';
		}
		if ($ltVal <= 0) {
			return '';
		}

		return $this->db->quoteInto("%filterField% <?", $ltVal);
	}

	/**
	 * 大于
	 * @param  array $oneFilterRule 备货规则的一条分类规则
	 * @return string
	 */
	private function _gtOperator(array $oneFilterRule) {
		list($operator, $gtVal) = [$oneFilterRule['operator'], $oneFilterRule['operator_max_param']];
		if ($operator !== 'gt') {
			return '';
		}
		return $this->db->quoteInto("%filterField% >?", $gtVal);
	}

	/**
	 * 在某个区间内
	 * @param  array $oneFilterRule 备货规则的一条分类规则
	 * @return string
	 */
	private function _betweenOperator(array $oneFilterRule) {
		list($operator, $ltVal, $gtVal) = [$oneFilterRule['operator'], $oneFilterRule['operator_min_param'], $oneFilterRule['operator_max_param']];
		if ($operator !== 'between') {
			return '';
		}
		if ($ltVal <= 0) {
			return '';
		}
		return $this->db->quoteInto("%filterField% >=?", $ltVal) . " AND " . $this->db->quoteInto("%filterField% <?", $gtVal);
	}

	/**
	 * 等于
	 * @param  array $oneFilterRule 备货规则的一条分类规则
	 * @return string
	 */
	private function _eqOperator(array $oneFilterRule) {
		list($operator, $eqVal) = [$oneFilterRule['operator'], $oneFilterRule['operator_eq_param']];
		if ($operator !== 'eq') {
			return '';
		}

		$eqVal = explode(',', $eqVal);
		if (count($eqVal) > 1) {
			return $this->db->quoteInto("%filterField% in (?)", $eqVal);
		}
		return $this->db->quoteInto("%filterField% =?", $eqVal);
	}
}